package dao;

import java.math.BigInteger;
import java.sql.Connection;
import java.sql.SQLException;
import java.util.List;

import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;

import model.Goods;
import model.OrderItem;
import model.Orders;
import utils.DataSourceUtils;

public class OrdersDao {
				// 创建事务对象 Connection
	public void  insertOrders(Connection con,Orders orders) throws SQLException {
		
		QueryRunner r = new QueryRunner();
		String sql = "insert into t_orders(total,amount,status,paytype,name,phone,address,datetime,user_id) values(?,?,?,?,?,?,?,?,?)";
		r.update(con, sql,
				orders.getTotal(),orders.getAmount(),orders.getStatus(),
				orders.getPaytype(),orders.getName(),orders.getPhone(),
				orders.getAddress(),orders.getDatetime(),orders.getUser().getId() );
	}

			// 获取新插入数据的Id
	public  int  getLastInsertId(Connection con) throws SQLException {
		
		QueryRunner r = new QueryRunner();
		
		String sql = "select last_insert_id()"; // 查询最后插入数据的Id
		
		BigInteger bi =  r.query(con, sql, new ScalarHandler<BigInteger>());
		
		return Integer.parseInt(bi.toString());
	}
	
	
	public  void  insertOrderItem(Connection con,OrderItem item) throws SQLException {
		
		QueryRunner r = new  QueryRunner();
		String sql = "insert into t_orderitem(price,amount,goods_id,orders_id) values(?,?,?,?)";
		r.update(con, sql,item.getPrice(),item.getAmount(),item.getGoods().getId(),item.getOrders().getId());
		
	}
				// 测试数据
//	public static void main(String[] args) throws SQLException {
//		
//		int id = new OrdersDao().getLastInsertId(DBUtil.getDataSource().getConnection());
//		System.out.println(id);
//	}
	
	public List<Orders> selectAll(int userid) throws SQLException {
		
		QueryRunner  r  = new   QueryRunner(DataSourceUtils.getDataSource());
		
		String sql = "select * from t_orders where user_id=? order by datetime desc"; // 查询所有的订单,根据下单时间倒序排
		
		return r.query(sql, new BeanListHandler<Orders>(Orders.class),userid);
		
		
	}
	
	public  List<OrderItem> selectAllItem(int ordersid) throws SQLException {
		
		QueryRunner  r  = new   QueryRunner(DataSourceUtils.getDataSource());
		
		String sql = "select i.id,i.price,i.amount,g.name from t_orderitem i,ware g where orders_id=? and i.goods_id=g.id;"; // 根据订单id,查询所有的订单项
		
		return r.query(sql, new BeanListHandler<OrderItem>(OrderItem.class),ordersid);
		
	}

	public int getOrdersCount(int status) throws SQLException {
		
	QueryRunner  r  = new  QueryRunner(DataSourceUtils.getDataSource());
	
	String	sql =  "";
	if(status==0) {
			sql  =  "select  count(*)  from  t_orders";
			return  r.query(sql, new ScalarHandler<Long>()).intValue();
	 
	}else {
		
		  sql  =  "select   count(*)  from  t_orders  where  status=?";
		  return  r.query(sql, new ScalarHandler<Long>(),status).intValue();
		
	}
	}

	public List<Orders> selectOrdersList(int status, int pageNo, int pageSize) throws SQLException {

		QueryRunner  r  = new QueryRunner(DataSourceUtils.getDataSource());
		if(status==0) {
			
			String	sql		= "select o.id,o.total,o.amount,o.status,o.paytype,o.name,o.phone,o.address,o.datetime,u.username from t_orders o,user u where o.user_id=u.id  order by o.datetime  desc  limit  ?,?";
			return   r.query(sql,new BeanListHandler<Orders>(Orders.class), (pageNo-1)*pageSize,pageSize);
		}else {
			String	sql		= "select o.id,o.total,o.amount,o.status,o.paytype,o.name,o.phone,o.address,o.datetime,u.username from t_orders o,user u where o.user_id=u.id and o.status=?  order by o.datetime  desc limit  ?,?";
		     return	r.query(sql,new BeanListHandler<Orders>(Orders.class),status, (pageNo-1)*pageSize,pageSize);
		}
	}
	
	public void  updateStatus(int id, int status) throws SQLException {
		
		QueryRunner  r = new  QueryRunner(DataSourceUtils.getDataSource());
		
		String sql = "update t_orders set status=? where id=?" ; // 修改订单状态
		
		r.update(sql,status,id);
		
		
	}
	
		// 进行事务控制
	public void  deleteOrders(Connection con,int id) throws SQLException {
		
		QueryRunner  r = new  QueryRunner();
		
		String sql = "delete  from t_orders where id=?";  // 删除订单
		
		r.update(con,sql,id);
		
	}
	
	public void  deleteOrdersItem(Connection con,int id) throws SQLException {
		
		QueryRunner  r = new  QueryRunner();
		
		String sql = "delete  from t_orderitem where orders_id=?"; 
		
		r.update(con,sql,id);
		
	}

}
